Imports System.Web

Public Class AnaPrat

    Inherits Anagrafica

    'dati anagrafici
    Private _idPrat As String
    Private _storico As Boolean



    Public Property idPrat() As String
        Get
            Return _idPrat
        End Get
        Set(ByVal Value As String)
            _idPrat = Value
        End Set
    End Property
    Public Property storico() As Boolean
        Get
            Return _storico
        End Get
        Set(ByVal Value As Boolean)
            _storico = Value
        End Set
    End Property


    'iscrizione
    Private _dataIsc As String
    Private _dataDomanda As String
    Private _dataDelibera As String
    Private _numDelibera As String
    Private _anzianita As String
    Private _dataInizioPrat As String
    Private _dataFinePrat As String


    Private _numIsc As String
    Private _note As String

    Public Property note() As String
        Get
            Return _note
        End Get
        Set(ByVal Value As String)
            _note = Value
        End Set
    End Property


    Public Property dataInizioPrat() As String
        Get
            Return _dataInizioPrat
        End Get
        Set(ByVal Value As String)
            _dataInizioPrat = Value
        End Set
    End Property
    Public Property dataFinePrat() As String
        Get
            Return _dataFinePrat
        End Get
        Set(ByVal Value As String)
            _dataFinePrat = Value
        End Set
    End Property


    Public Property numIsc() As String
        Get
            Return _numIsc
        End Get
        Set(ByVal Value As String)
            _numIsc = Value
        End Set
    End Property

    Public Property dataIsc() As String
        Get
            Return _dataIsc
        End Get
        Set(ByVal Value As String)
            _dataIsc = Value
        End Set
    End Property
    Public Property dataDomanda() As String
        Get
            Return _dataDomanda
        End Get
        Set(ByVal Value As String)
            _dataDomanda = Value
        End Set
    End Property


    Public Property dataDelibera() As String
        Get
            Return _dataDelibera
        End Get
        Set(ByVal Value As String)
            _dataDelibera = Value
        End Set
    End Property
    Public Property numDelibera() As String
        Get
            Return _numDelibera
        End Get
        Set(ByVal Value As String)
            _numDelibera = Value
        End Set
    End Property

    Public Property anzianita() As String
        Get
            Return _anzianita
        End Get
        Set(ByVal Value As String)
            _anzianita = Value
        End Set
    End Property



    'altro
    Private _rel1 As Boolean
    Private _dataRel1 As String
    Private _rel2 As Boolean
    Private _dataRel2 As String
    Private _rel3 As Boolean
    Private _dataRel3 As String
    Private _rel4 As Boolean
    Private _dataRel4 As String
    Private _relFinale As Boolean
    Private _dataRelFinale As String


    'solo per periti
    Private _spec As String
    Public Property spec() As String
        Get
            Return _spec
        End Get
        Set(ByVal Value As String)
            _spec = Value
        End Set
    End Property

    Public Property rel1() As Boolean
        Get
            Return _rel1
        End Get
        Set(ByVal Value As Boolean)
            _rel1 = Value
        End Set
    End Property
    Public Property dataRel1() As String
        Get
            Return _dataRel1
        End Get
        Set(ByVal Value As String)
            _dataRel1 = Value
        End Set
    End Property
    Public Property rel2() As Boolean
        Get
            Return _rel2
        End Get
        Set(ByVal Value As Boolean)
            _rel2 = Value
        End Set
    End Property
    Public Property dataRel2() As String
        Get
            Return _dataRel2
        End Get
        Set(ByVal Value As String)
            _dataRel2 = Value
        End Set
    End Property
    Public Property rel3() As Boolean
        Get
            Return _rel3
        End Get
        Set(ByVal Value As Boolean)
            _rel3 = Value
        End Set
    End Property
    Public Property dataRel3() As String
        Get
            Return _dataRel3
        End Get
        Set(ByVal Value As String)
            _dataRel3 = Value
        End Set
    End Property
    Public Property rel4() As Boolean
        Get
            Return _rel4
        End Get
        Set(ByVal Value As Boolean)
            _rel4 = Value
        End Set
    End Property
    Public Property dataRel4() As String
        Get
            Return _dataRel4
        End Get
        Set(ByVal Value As String)
            _dataRel4 = Value
        End Set
    End Property
    Public Property relFinale() As Boolean
        Get
            Return _relFinale
        End Get
        Set(ByVal Value As Boolean)
            _relFinale = Value
        End Set
    End Property
    Public Property dataRelFinale() As String
        Get
            Return _dataRelFinale
        End Get
        Set(ByVal Value As String)
            _dataRelFinale = Value
        End Set
    End Property

    Public Function getNumero() As String
        Dim s As String = getValore("select numPrat from numIscrizione where albo = " & HttpContext.Current.Session("albo"))
        Dim n As Int32
        If s <> "" Then
            n = CInt(s) + 1

        End If

        Return CStr(n)

    End Function

    Public Function updateNumero(ByVal n As String)

        eseguiSql("update numIscrizione set numPrat=" & n & " where albo = " & HttpContext.Current.Session("albo"))

    End Function


    Public Function checkNumIsc() As Boolean
        Dim n As String = getValore("select Praticanti.id from Praticanti INNER JOIN Anagrafica ON Praticanti.Id_ana = Anagrafica.ID where Praticanti.num_isc = " & _numIsc & " and Anagrafica.albo=" & HttpContext.Current.Session("albo"))
        If Not (IsNothing(n)) Then
            If n = _idPrat Then
                Return True
            Else
                Return False
            End If
        Else
            Return True
        End If
    End Function
    Public Function elencoProvv() As DataTable
        Dim sql As String = "SELECT Provvedimenti.Id, TipoProvvedimenti.descrizione as tipo, Provvedimenti.delibera, convert(char(10),data,103) as data " & _
            " FROM  Provvedimenti INNER JOIN TipoProvvedimenti ON Provvedimenti.tipo_provv = TipoProvvedimenti.codice " & _
            " WHERE Provvedimenti.Id_prat = " & _idPrat & " order by Provvedimenti.data"
        Return getDataTable(sql)
    End Function

    Public Function elencoSosp() As DataTable
        Dim sql As String = "SELECT id, tipoSospensione.descrizione as motivo, convert(char(10),dataInizio,103) as dataInizio, " & _
            "convert(char(10),dataFine,103) as dataFine  FROM  Sospensioni inner join tipoSospensione on Sospensioni.tipo = tipoSospensione.codice WHERE Id_prat = " & _idPrat
        Return getDataTable(sql)
    End Function

    Public Function elencoTipoSospensione() As DataTable
        Return getDataTable("select null as codice, '' as descrizione union select codice, descrizione from tipoSospensione")
    End Function

    Public Function elencoTipoCorso() As DataTable
        Return getDataTable("select * from tipoCorso")
    End Function

    Public Function elencoContratto() As DataTable
        Return getDataTable("select null as codice, '' as descrizione union select codice, descrizione from tipoContratto")
    End Function

    Public Function elencoMotiviAb() As DataTable
        Return getDataTable("select null as codice, '' as descrizione union select codice, descrizione from tipoMotivoAB")
    End Function

    Public Function elencoMotiviStorico(ByVal tutti As Boolean) As DataTable
        If tutti Then
            Return getDataTable("select null as codice, '' as descrizione union select codice, descrizione from tipoMotivoStorico")
        Else
            Return getDataTable("select null as codice, '' as descrizione union select codice, descrizione from tipoMotivoStorico where codice>1")
        End If

    End Function

    Public Function elencoTipoEsito() As DataTable
        Return getDataTable("select null as codice, '' as descrizione union select codice, descrizione from tipoEsito")
    End Function


    Public Function elencoAts() As DataTable
        Dim sql As String = "SELECT id, presso, convert(char(10),dataInizio,103) as dataInizio, " & _
            "convert(char(10),dataFine,103) as dataFine  FROM  attSub WHERE Id_prat = " & _idPrat
        Return getDataTable(sql)
    End Function

    Public Sub caricaPrat()
        Dim sql As String = "SELECT spec, storico, num_isc, convert(char(10),data_isc,103) as data_isc, convert(char(10),data_domanda,103) as data_domanda, " & _
            " convert(char(10),data_delib,103) as data_delib, " & _
            "num_delib,  anzianita,  rel1, rel2, rel3, rel4, relFinale,   " & _
            "convert(char(10),data_rel_1,103) as data_rel_1, " & _
            "convert(char(10),data_rel_2,103) as data_rel_2, " & _
            "convert(char(10),data_rel_3,103) as data_rel_3, " & _
            "convert(char(10),data_rel_finale,103) as data_rel_finale, " & _
            "convert(char(10),data_rel_4,103) as data_rel_4, note, " & _
            "convert(char(10), data_inizio_prat, 103) as dataInizioPrat, convert(char(10), data_fine_prat, 103) as dataFinePrat " & _
            "FROM  Praticanti  WHERE Praticanti.Id =" & _idPrat

        Dim dt As DataTable = getDataTable(sql)
        _spec = Utility.sNull(dt.Rows(0)("spec"))
        _storico = dt.Rows(0)("storico")
        _numIsc = dt.Rows(0)("num_isc")
        _dataIsc = Utility.sNull(dt.Rows(0)("data_isc"))
        _dataDomanda = Utility.sNull(dt.Rows(0)("data_domanda"))
        _dataDelibera = Utility.sNull(dt.Rows(0)("data_delib"))
        _numDelibera = Utility.sNull(dt.Rows(0)("num_delib"))
        _anzianita = Utility.sNull(dt.Rows(0)("anzianita"))
        _rel1 = Utility.sNull(dt.Rows(0)("rel1"))
        _dataRel1 = Utility.sNull(dt.Rows(0)("data_rel_1"))
        _rel2 = Utility.sNull(dt.Rows(0)("rel2"))
        _dataRel2 = Utility.sNull(dt.Rows(0)("data_rel_2"))
        _rel3 = Utility.sNull(dt.Rows(0)("rel3"))
        _dataRel3 = Utility.sNull(dt.Rows(0)("data_rel_3"))
        _rel4 = Utility.sNull(dt.Rows(0)("rel4"))
        _dataRel4 = Utility.sNull(dt.Rows(0)("data_rel_4"))
        _relFinale = Utility.sNull(dt.Rows(0)("relFinale"))
        _dataRelFinale = Utility.sNull(dt.Rows(0)("data_rel_finale"))
        _note = Utility.sNull(dt.Rows(0)("note"))
        _dataInizioPrat = Utility.sNull(dt.Rows(0)("dataInizioPrat"))
        _dataFinePrat = Utility.sNull(dt.Rows(0)("dataFinePrat"))

    End Sub




    Public Function caricaElencoProf() As DataTable
        Dim sql As String = "SELECT PratProf.id, Professionisti.Nome, Professionisti.Citta, " & _
                "convert(char(10),PratProf.Data_inizio,103) as data_inizio, " & _
                "convert(char(10),PratProf.Data_fine,103) as Data_fine " & _
                "FROM PratProf INNER JOIN Professionisti ON PratProf.id_prof = Professionisti.Id where id_prat =" & _idPrat
        Return getDataTable(sql)

    End Function

    Public Sub inserisciStorico(ByVal _causale As String, ByVal _dataS As String, ByVal _noteS As String, ByVal _numCanc As String, ByVal _dataCanc As String)
        Dim sql As String = " insert into storicoPraticanti (id_prat, causale, data, numCanc, dataCanc, note, utente) " & _
                " values (@idPrat, @causale, @data, @numCanc, @dataCanc, @note, @utente)"

        Dim cmd As SqlClient.SqlCommand = New SqlClient.SqlCommand(sql, getConnection)
        cmd.Parameters.Add("@idPrat", _idPrat)
        cmd.Parameters.Add("@causale", IIf(_causale = "", SqlTypes.SqlDateTime.Null, _causale))
        cmd.Parameters.Add("@data", IIf(_dataS = "", SqlTypes.SqlDateTime.Null, Utility.FormattaDataxDB(_dataS)))
        cmd.Parameters.Add("@numCanc", _numCanc)
        cmd.Parameters.Add("@dataCanc", IIf(_dataCanc = "", SqlTypes.SqlDateTime.Null, Utility.FormattaDataxDB(_dataCanc)))
        cmd.Parameters.Add("@note", _noteS)
        cmd.Parameters.Add("@Utente", System.Security.Principal.WindowsIdentity.GetCurrent.Name)
        cmd.ExecuteNonQuery()
        cmd.Connection.Close()

        eseguiSql("update Praticanti set storico = 1 where id=" & _idPrat)

    End Sub

    Public Sub aggiornaStorico(ByVal _idStorico As String, ByVal _causale As String, ByVal _dataS As String, ByVal _noteS As String, ByVal _numCanc As String, ByVal _dataCanc As String)
        Dim sql As String = " update storicoPraticanti set causale=@causale, data=@data, numCanc=@numCanc, dataCanc=@dataCanc, note=@note, utente=@utente " & _
                " where id=@id"

        Dim cmd As SqlClient.SqlCommand = New SqlClient.SqlCommand(sql, getConnection)
        cmd.Parameters.Add("@id", _idStorico)
        cmd.Parameters.Add("@causale", IIf(_causale = "", SqlTypes.SqlDateTime.Null, _causale))
        cmd.Parameters.Add("@data", IIf(_dataS = "", SqlTypes.SqlDateTime.Null, Utility.FormattaDataxDB(_dataS)))
        cmd.Parameters.Add("@numCanc", _numCanc)
        cmd.Parameters.Add("@dataCanc", IIf(_dataCanc = "", SqlTypes.SqlDateTime.Null, Utility.FormattaDataxDB(_dataCanc)))
        cmd.Parameters.Add("@note", _noteS)
        cmd.Parameters.Add("@Utente", System.Security.Principal.WindowsIdentity.GetCurrent.Name)
        cmd.ExecuteNonQuery()
        cmd.Connection.Close()

    End Sub

    Public Function caricaStorico() As DataTable
        Dim sql = "select id, convert(char(10),data,103) as data, " & _
            "convert(char(10),dataCanc,103) as dataCanc," & _
            "causale, numcanc, note from storicoPraticanti where id_prat=" & _idPrat


        Return getDataTable(sql)

    End Function

    Public Sub aggiornaStorico(ByVal _idS As String, ByVal _causale As String, ByVal _dataS As String, ByVal _noteS As String)
        Dim sql As String = " update storicoPraticanti set causale=@causale, data=@data, note=@note, utente=@utente where id=" & _idS

        Dim cmd As SqlClient.SqlCommand = New SqlClient.SqlCommand(sql, getConnection)
        cmd.Parameters.Add("@causale", _causale)
        cmd.Parameters.Add("@data", IIf(_dataS = "", SqlTypes.SqlDateTime.Null, Utility.FormattaDataxDB(_dataS)))
        cmd.Parameters.Add("@note", _noteS)
        cmd.Parameters.Add("@Utente", System.Security.Principal.WindowsIdentity.GetCurrent.Name)
        cmd.ExecuteNonQuery()
        cmd.Connection.Close()
    End Sub

    Public Sub inserisciPrat()
        Dim sql As String

        _idPrat = eseguiSPInsert("insertAnaPrat")

        aggiornaPrat()

    End Sub

    Public Sub aggiornaDatePrat()
        Dim sql As String = "update Praticanti set data_inizio_prat=@dataInizioPrat, data_fine_prat=@dataFinePrat, utente=@utente where id=" & _idPrat
        Dim cmd As SqlClient.SqlCommand = New SqlClient.SqlCommand(sql, getConnection)

        cmd.Parameters.Add("@dataInizioPrat", IIf(_dataInizioPrat = "", SqlTypes.SqlDateTime.Null, Utility.FormattaDataxDB(_dataInizioPrat)))
        cmd.Parameters.Add("@dataFinePrat", IIf(_dataFinePrat = "", SqlTypes.SqlDateTime.Null, Utility.FormattaDataxDB(_dataFinePrat)))
        cmd.Parameters.Add("@Utente", System.Security.Principal.WindowsIdentity.GetCurrent.Name)

        cmd.ExecuteNonQuery()
        cmd.Connection.Close()

    End Sub
    Public Sub aggiornaPrat()
        Dim sql As String
        sql = "update Praticanti set  id_ana = @idAna, spec=@spec, num_isc =@numIsc, data_isc=@dataIsc, data_domanda=@dataDomanda " & _
                 ", data_delib = @dataDelibera , num_delib =@numDelibera ,  " & _
                    " anzianita=@anzianita, data_rel_1 =@dataRel1 , data_rel_2 = @dataRel2 , data_rel_3 = @dataRel3 " & _
                    ", data_rel_finale = @dataRelFinale , data_rel_4 = @dataRel4 , rel1= @rel1 , rel2= @rel2, rel3=@rel3, rel4=@rel4 , relfinale = @relFinale " & _
                     ",   note=@note, utente=@utente  where id =" & _idPrat


        Dim cmd As SqlClient.SqlCommand = New SqlClient.SqlCommand(sql, getConnection)
        cmd.Parameters.Add("@idAna", idAna)
        cmd.Parameters.Add("@spec", _spec)
        cmd.Parameters.Add("@numIsc", _numIsc)
        cmd.Parameters.Add("@dataIsc", IIf(_dataIsc = "", SqlTypes.SqlDateTime.Null, Utility.FormattaDataxDB(_dataIsc)))
        cmd.Parameters.Add("@dataDomanda", IIf(_dataDomanda = "", SqlTypes.SqlDateTime.Null, Utility.FormattaDataxDB(_dataDomanda)))
        cmd.Parameters.Add("@dataDelibera", IIf(_dataDelibera = "", SqlTypes.SqlDateTime.Null, Utility.FormattaDataxDB(_dataDelibera)))
        cmd.Parameters.Add("@numDelibera", _numDelibera)
        cmd.Parameters.Add("@anzianita", _anzianita)
        cmd.Parameters.Add("@dataRel1", IIf(_dataRel1 = "", SqlTypes.SqlDateTime.Null, Utility.FormattaDataxDB(_dataRel1)))
        cmd.Parameters.Add("@dataRel2", IIf(_dataRel2 = "", SqlTypes.SqlDateTime.Null, Utility.FormattaDataxDB(_dataRel2)))
        cmd.Parameters.Add("@dataRel3", IIf(_dataRel3 = "", SqlTypes.SqlDateTime.Null, Utility.FormattaDataxDB(_dataRel3)))
        cmd.Parameters.Add("@dataRelFinale", IIf(_dataRelFinale = "", SqlTypes.SqlDateTime.Null, Utility.FormattaDataxDB(_dataRelFinale)))
        cmd.Parameters.Add("@dataRel4", IIf(_dataRel4 = "", SqlTypes.SqlDateTime.Null, Utility.FormattaDataxDB(_dataRel4)))
        cmd.Parameters.Add("@rel1", _rel1)
        cmd.Parameters.Add("@rel2", _rel2)
        cmd.Parameters.Add("@rel3", _rel3)
        cmd.Parameters.Add("@relFinale", _relFinale)
        cmd.Parameters.Add("@rel4", _rel4)
        cmd.Parameters.Add("@note", _note)
        cmd.Parameters.Add("@Utente", System.Security.Principal.WindowsIdentity.GetCurrent.Name)

        cmd.ExecuteNonQuery()
        cmd.Connection.Close()

    End Sub
    Public Sub setRelazioni()
        Dim sSql As String = "UPDATE Praticanti SET Data_rel_1 = @Data_rel_1, Data_rel_2 = @Data_rel_2, Data_rel_3 = @Data_rel_3, Data_rel_finale = @Data_rel_finale, Data_rel_4 = @Data_rel_4, Rel1 = @Rel1, Rel2 = @Rel2, Rel3 = @Rel3, RelFinale = @Rel_Finale, Rel4 = @Rel4 WHERE Id=" & _idPrat
        Dim comando As New Data.SqlClient.SqlCommand(sSql)
        comando.Parameters.Add("@Data_rel_1", System.Data.SqlDbType.DateTime)
        comando.Parameters.Add("@Data_rel_2", System.Data.SqlDbType.DateTime)
        comando.Parameters.Add("@Data_rel_3", System.Data.SqlDbType.DateTime)
        comando.Parameters.Add("@Data_rel_4", System.Data.SqlDbType.DateTime)
        comando.Parameters.Add("@Data_rel_Finale", System.Data.SqlDbType.DateTime)
        comando.Parameters.Add("@Rel1", System.Data.SqlDbType.Bit)
        comando.Parameters.Add("@Rel2", System.Data.SqlDbType.Bit)
        comando.Parameters.Add("@Rel3", System.Data.SqlDbType.Bit)
        comando.Parameters.Add("@Rel4", System.Data.SqlDbType.Bit)
        comando.Parameters.Add("@Rel_Finale", System.Data.SqlDbType.Bit)
        If IsDate(_dataRel1) Then
            comando.Parameters("@Data_rel_1").Value = Date.Parse(_dataRel1)
        Else
            comando.Parameters("@Data_rel_1").Value = System.DBNull.Value
        End If
        If IsDate(_dataRel2) Then
            comando.Parameters("@Data_rel_2").Value = Date.Parse(_dataRel1)
        Else
            comando.Parameters("@Data_rel_2").Value = System.DBNull.Value
        End If
        If IsDate(_dataRel3) Then
            comando.Parameters("@Data_rel_3").Value = Date.Parse(_dataRel1)
        Else
            comando.Parameters("@Data_rel_3").Value = System.DBNull.Value
        End If
        If IsDate(_dataRel4) Then
            comando.Parameters("@Data_rel_4").Value = Date.Parse(_dataRel1)
        Else
            comando.Parameters("@Data_rel_4").Value = System.DBNull.Value
        End If
        If IsDate(_dataRelFinale) Then
            comando.Parameters("@Data_rel_Finale").Value = Date.Parse(_dataRel1)
        Else
            comando.Parameters("@Data_rel_Finale").Value = System.DBNull.Value
        End If
        comando.Parameters("@Rel1").Value = _rel1
        comando.Parameters("@Rel2").Value = _rel2
        comando.Parameters("@Rel3").Value = _rel3
        comando.Parameters("@Rel4").Value = _rel4
        comando.Parameters("@Rel_Finale").Value = _relFinale
        MyBase.ExecuteNonQuerySqlCommand(comando)

    End Sub
    Public Function caricaPraticantato() As DataTable
        Dim sql As String = "SELECT id, tipo, 'Praticantato' as tipoP, " & _
           " convert(char(10),Data_inizio, 103) as dataI, " & _
           " convert(char(10),Data_fine, 103) as dataF, " & _
           " data_inizio as dataInizio, data_fine as dataFine, crediti  FROM PratProf WHERE tipo<>2 and id_prat = " & _idPrat & _
           " union " & _
           " SELECT id, 0 as tipo, 'Att.Tec.Sub.' as tipoP, " & _
           " convert(char(10),Data_inizio, 103) as dataI, " & _
           " convert(char(10),Data_fine, 103) as dataF, " & _
           " data_inizio as dataInizio, data_fine as dataFine, crediti  FROM attSub WHERE id_prat = " & _idPrat & _
           " union SELECT id,  0 as tipo,'Percorso formativo' as tipoP, " & _
           "'' as dataI, " & _
           " '' dataF," & _
           " null as dataInizio, null as dataFine, crediti FROM PercorsoForm WHERE id_prat = " & _idPrat & _
           " union SELECT Id, 0 as tipo, 'Sospensione' as tipoP,convert(char(10),DataInizio,103) as dataI, " & _
           " convert(char(10),DataFine,103) as dataF, " & _
           " dataInizio, dataFine, tot as crediti FROM Sospensioni WHERE Id_prat = " & _idPrat & " order by dataInizio "

        Return getDataTable(sql)
    End Function

    Public Function aggDataFine() As String
        Dim sql As String = "select tipo, datediff(d,data_inizio, data_fine) +1  as giorni, data=case when data_inizio is null then '' else convert(char(10), data_inizio, 103) end, " & _
                "dataF=case when data_fine is null then '' else convert(char(10), data_fine, 103) end, data_inizio  from pratprof where id_prat= " & _idPrat & _
                "union select 2 as tipo, datediff(d,data_inizio, data_fine) +1  as giorni, data=case when data_inizio is null then '' else convert(char(10), data_inizio, 103) end, " & _
                "dataF=case when data_fine is null then '' else convert(char(10), data_fine, 103) end , data_inizio from attsub where id_prat=  " & _idPrat & _
                "ORDER by data_inizio"
        Dim dt As DataTable = getDataTable(sql)

        Dim objC As New cConfigura(False)
        Dim totG As Int16 = objC.conf.totalePraticantato
        Dim giorniProf As Int16 = objC.conf.giorniProf
        Dim giorniDitta As Int16 = objC.conf.giorniDitta


        Dim rit As String

        Dim dr As DataRow
        Dim tot As Int16 = 0
        Dim rimanenti As Double = 0
        Dim giorniPF As String = getValore("select sum(crediti) from percorsoForm where id_prat= " & _idPrat)
        If giorniPF <> "" Then
            tot = Int(giorniPF)
        End If

        For Each dr In dt.Rows
            If Not dr("giorni") Is System.DBNull.Value Then
                If dr("tipo") <> 2 Then
                    'professionista
                    tot += (dr("giorni") * totG) \ giorniProf
                Else
                    'ditta
                    tot += (dr("giorni") * totG) \ giorniDitta
                End If
            Else
                If Trim(dr("data")) <> "" Then
                    rimanenti = totG - tot
                    'calcolo data presunta
                    If dr("tipo") <> 2 Then
                        'professionista
                        rimanenti = (rimanenti * giorniProf) \ totG
                    Else
                        'ditta
                        rimanenti = (rimanenti * giorniDitta) \ totG
                    End If
                    If rimanenti > 0 Then rimanenti -= 1
                    rit = Utility.addData(dr("data"), rimanenti)
                    Exit For
                Else
                    rit = ""
                End If
            End If
        Next

        Return rit

    End Function


    Public Function caricaEsami() As DataTable
        Return getDataTable("SELECT EsamiStato.Id, ltrim(str(EsamiStato.Anno)) + ' ' + EsamiStato.Num_sessione as sessione, Istituto.Denominazione AS presso " & _
                " FROM EsamiStato left JOIN Istituto ON EsamiStato.Presso = Istituto.Id where esamiStato.eliminato=0 ")
    End Function

    Public Function elencoSessioni() As DataTable
        Return getDataTable("select null as id, '' as descrizione union SELECT EsamiStato.Id, LTRIM(STR(EsamiStato.Anno)) + ' ' + EsamiStato.Num_sessione as descrizione FROM EsamiStato where esamiStato.eliminato=0 ")
    End Function
    Public Function caricaEsitiEsami() As DataTable
        Dim sql As String = "SELECT EsitoEsami.id, ltrim(str(EsamiStato.Anno)) + ' ' + EsamiStato.Num_sessione as sessione, Istituto.Denominazione as Presso, TipoEsito.descrizione  as esito " & _
            " FROM EsitoEsami INNER JOIN EsamiStato ON EsitoEsami.Id_esame = EsamiStato.Id INNER JOIN TipoEsito ON EsitoEsami.Esito = TipoEsito.codice LEFT JOIN Istituto ON EsamiStato.Presso = Istituto.Id " & _
            " WHERE EsitoEsami.Id_prat =" & _idPrat
        Return getDataTable(sql)
    End Function

    Public Function getIDPraticante(ByVal idAna As String) As String
        Dim obj As AnaPrat = New AnaPraticanti(True).Anagrafica
        Return (New Connessione).getValore(" Select ID FROM Praticanti WHERE Id_ana = " & idAna)
    End Function
End Class

Public Class AnaPraticanti
    Inherits Connessione

    Private _anag As AnaPrat


    Public Sub New(Optional ByVal newInstance As Boolean = False)

        If (HttpContext.Current.Session("ANAPRAT") Is Nothing Or newInstance) Then
            ' new instance here ;)
            _anag = New AnaPrat
            HttpContext.Current.Session("ANAPRAT") = _anag
        Else
            _anag = HttpContext.Current.Session("ANAPRAT")
        End If
    End Sub


    Public Property Anagrafica() As AnaPrat
        Get
            Return _anag
        End Get
        Set(ByVal Value As AnaPrat)
            _anag = Value
        End Set
    End Property


    Public Function elencoPraticanti(ByVal ordine As String, ByVal filtro As String, ByVal tutti As Int16, ByVal tipoRicerca As String) As DataTable
        Dim sql As String = "SELECT  Praticanti.ID as id_prat, Praticanti.Id_ana, Anagrafica.Cognome + ' ' + Anagrafica.Nome as nominativo,convert(char(10), data_nascita, 103) as data_nascita, convert(int,Praticanti.Num_isc) as Num_isc " & _
            "FROM  Anagrafica INNER JOIN Praticanti ON Anagrafica.ID = Praticanti.Id_ana where Anagrafica.albo= " & HttpContext.Current.Session("albo")
        Dim sqlWhere As String = ""
        Select Case tutti
            Case 0 'attivi
                sqlWhere += " and Praticanti.storico = 0"
            Case 1 'storico
                sqlWhere += " and Praticanti.storico = 1"
            Case 2 'tutti
        End Select
        If tipoRicerca = "Inizia con" Then

            If filtro <> "" Then
                If sqlWhere <> "" Then
                    sqlWhere += " and cognome like '" & Replace(filtro, "'", "''") & "%' "
                Else
                    sqlWhere += " and cognome like '" & Replace(filtro, "'", "''") & "%' "
                End If
            End If
            sql += sqlWhere + " ORDER BY " & ordine
        End If

        If tipoRicerca = "Contiene" Then

            If filtro <> "" Then
                If sqlWhere <> "" Then
                    sqlWhere += " and cognome + ' ' + nome like '%" & Replace(filtro, "'", "''") & "%' "
                Else
                    sqlWhere += " and cognome + ' ' + nome like '%" & Replace(filtro, "'", "''") & "%' "
                End If
            End If
            sql += sqlWhere + " ORDER BY " & ordine
        End If
        Return getDataTable(sql)
    End Function

    Public Function elencoPraticantiPerNumero(ByVal ordine As String, ByVal filtro As String, ByVal tutti As Int16, ByVal tipoRicerca As String) As DataTable
        Dim sql As String = "SELECT  Praticanti.ID as id_prat, Praticanti.Id_ana, Anagrafica.Cognome + ' ' + Anagrafica.Nome as nominativo,convert(char(10), data_nascita, 103) as data_nascita, Praticanti.Num_isc " & _
            "FROM  Anagrafica INNER JOIN Praticanti ON Anagrafica.ID = Praticanti.Id_ana where Anagrafica.albo= " & HttpContext.Current.Session("albo")
        Dim sqlWhere As String = ""
        Select Case tutti
            Case 0 'attivi
                sqlWhere += " and Praticanti.storico = 0"
            Case 1 'storico
                sqlWhere += " and Praticanti.storico = 1"
            Case 2 'tutti
        End Select
        If filtro <> "" Then
            If sqlWhere <> "" Then
                sqlWhere += " and Num_isc like '" & Replace(filtro, "'", "''") & "%' "
            Else
                sqlWhere += " and Num_isc like '" & Replace(filtro, "'", "''") & "%' "
            End If
        End If
        sql += sqlWhere + " ORDER BY Num_isc"
        Return getDataTable(sql)
    End Function

   
End Class
